package com.roc.web.timer;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Random;

import org.apache.log4j.Logger;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.dao.black.IBlackDao;
import com.roc.dao.diaodan.IDiaoDanDao;
import com.roc.dao.setting.ISettingFlagDao;
import com.roc.pojo.AuditingItem;
import com.roc.pojo.DiaoDanBean;
import com.roc.pojo.MessageSend;
import com.roc.pojo.Operators;
import com.roc.pojo.ProfitShare;
import com.roc.pojo.SettingBean;
import com.roc.pojo.SettingItemBean;
import com.roc.pojo.TransReport;
import com.roc.service.IMerchantServices;
import com.roc.service.IProfitShareService;
import com.roc.service.ServiceException;
import com.roc.service.diaodan.IDiaodanService;
import com.roc.service.setting.ISettflagService;

import com.roc.util.Constance;
import com.roc.util.DateUtil;
import com.roc.util.ExcelUtil;
import com.roc.util.Utils;

import com.roc.web.action.profitShare.ProfitShareQueryAction;

@Service
@Transactional
public class CalcuUtil {
	private Logger logger = Logger.getLogger(CalcuUtil.class);

	@Autowired
	private ISettingFlagDao settingFlagDao;
	@Autowired
	private IDiaoDanDao diaodanDao;
	@Autowired
	private IBaseDao baseDao;
	@Autowired
	ISQLBaseDao sqlBaseDao;
	@Autowired
	IDiaodanService diaodanService;
	@Autowired
	private IBlackDao blackDao;
	@Autowired
	private IMerchantServices imerchant;
	@Autowired
	IProfitShareService profitShareService;
	@Autowired
	ISettflagService settflagserevice;

	public void excuCalcuPro() throws ServiceException {

		try {
			System.out.print("service");
			settingFlagDao.excuCalcuPro();
		} catch (DaoException e) {
			logger.debug(e.getMessage());

			throw new ServiceException("执行失败~！");
		}
	}

	public void excuSettingPro() throws ServiceException {

		try {
			System.out.print("service");
			settingFlagDao.excuSettingPro();
		} catch (DaoException e) {
			logger.debug(e.getMessage());

			throw new ServiceException("执行失败~！");
		}
	}
	public void excuSettingLoadPro() throws ServiceException {

		try {
			
			settingFlagDao.excuSettingLoadPro();
		} catch (DaoException e) {
			logger.debug(e.getMessage());

			throw new ServiceException("执行失败~！");
		}
	}
	public void excuCleanDatePro() throws ServiceException {

		try {
			settingFlagDao.excuCleanDatePro();
		} catch (DaoException e) {
			logger.debug(e.getMessage());

			throw new ServiceException("执行失败~！");
		}
	}

	// 获取出款信息
	public List<SettingItemBean> querySettingItemList() throws ServiceException {
		List<SettingItemBean> settingitemList = new ArrayList<SettingItemBean>();

		try {

			String sql = "select t.* from t_settle_item t, t_transjnls_settle ts where t.merchantno = ts.merchantno and t.settledate = t.settledate and ts.settleflag = 'W'   and t.settid = ts.id ";
			List<Object> objList = new ArrayList<Object>();
			objList = sqlBaseDao.queryAll(sql);
			if (objList != null && objList.size() > 0) {
				Iterator iterator = objList.iterator();
				while (iterator.hasNext()) {
					Object[] obj = (Object[]) iterator.next();
					SettingItemBean sett = new SettingItemBean();
					if (obj[0] != null && !"".equals(obj[0])) {
						sett.setId(Long.valueOf(obj[0].toString()));
					}
					if (obj[1] != null && !"".equals(obj[1])) {
						sett.setSerial(obj[1].toString());
					}
					if (obj[2] != null && !"".equals(obj[2])) {
						sett.setAccountno(obj[2].toString());
					}
					if (obj[3] != null && !"".equals(obj[3])) {
						sett.setAccountname(obj[3].toString());
					}
					if (obj[4] != null && !"".equals(obj[4])) {
						sett.setAmt(obj[4].toString());
					}
					if (obj[5] != null && !"".equals(obj[5])) {
						sett.setBankname(obj[5].toString());
					}
					if (obj[6] != null && !"".equals(obj[6])) {
						sett.setBanklinkno(obj[6].toString());
					}
					// if (obj[7] != null && !"".equals(obj[7])) {
					// sett.setRemark(obj[7].toString());
					// }
					if (obj[8] != null && !"".equals(obj[8])) {
						String time = "";
						Date dt = new Date();
						SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
						time = sdf.format(dt);
						sett.setSettledate(time);
					}
					// if (obj[9] != null && !"".equals(obj[9])) {
					// sett.setSystime()(obj[9].toString());
					// }
					if (obj[11] != null && !"".equals(obj[11])) {
						sett.setDelval(obj[11].toString());
					}

					settingitemList.add(sett);
				}
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage());

			throw new ServiceException("查询结算标示失败~！");
		}
		return settingitemList;
	}

	public void updatesetting(String id) throws ServiceException {
		try {
			settingFlagDao.updatesetting(id);
		} catch (DaoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// 修改出款表状态
	public void updatesetitem(String serial, String settdate, String struts)
			throws ServiceException {
		try {
			settingFlagDao.updatesetitem(serial, settdate, struts);
		} catch (DaoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// 结算表状态
	public void updatesetting(String serial, String settdate, String struts)
			throws ServiceException {
		try {
			settingFlagDao.updatesetting(serial, settdate, struts);
		} catch (DaoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// 查询出款成功条数
	public int setitemcount(String serial, String settdate)
			throws ServiceException {
		int i = 0;
		try {
			i = settingFlagDao.setitemcount(serial, settdate);
		} catch (DaoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	// 查询一条结算在出款表中的条数
	public int setitemtotalcount(String serial, String settdate)
			throws ServiceException {
		int i = 0;
		try {
			i = settingFlagDao.setitemtotalcount(serial, settdate);
		} catch (DaoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	// 获取状态为出款中的信息
	public List<SettingItemBean> queryCKstutsList() throws ServiceException {
		List<SettingItemBean> settingitemList = new ArrayList<SettingItemBean>();

		try {

			String sql = "select t.serial,ts.setdate  from t_settle_item t, t_transjnls_settle ts where t.merchantno = ts.merchantno and t.settledate = ts.settledate and t.status='D'  and t.settid = ts.id and rownum<=200  order by t.times  ";
			List<Object> objList = new ArrayList<Object>();
			objList = sqlBaseDao.queryAll(sql);
			if (objList != null && objList.size() > 0) {
				Iterator iterator = objList.iterator();
				while (iterator.hasNext()) {
					Object[] obj = (Object[]) iterator.next();
					SettingItemBean sett = new SettingItemBean();

					if (obj[0] != null && !"".equals(obj[0])) {
						sett.setSerial(obj[0].toString());
					}
					if (obj[1] != null && !"".equals(obj[1])) {
						sett.setSettledate(obj[1].toString());
					}
					settingitemList.add(sett);
				}
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage());

			throw new ServiceException("查询状态失败~！");
		}
		return settingitemList;
	}

	// 定时跑调单
	public void excuDiaodan() throws ServiceException {
		// Operators
		// logingMerchant=(Operators)ServletActionContext.getRequest().getSession().getAttribute(Constant.SESSION_LOGIN_MERCHANT);
		try {
			List<DiaoDanBean> list = new ArrayList<DiaoDanBean>();
			list = diaodanDao.queryDiaodan();
			for (int i = 0; i < list.size(); i++) {
				DiaoDanBean diaoDanBean = new DiaoDanBean();
				diaoDanBean = list.get(i);
				List<String> settidlist = new ArrayList<String>();
				// 修改调单信息表。
				String stuts = diaoDanBean.getStatus();
				List<SettingBean> settlist = diaodanService
						.querySettinglist(diaoDanBean.getMerchantno());
				// 调单金额
				double diaoamount = diaoDanBean.getDDamount();
				// 已冻结金额
				double diaofrozen = diaoDanBean.getFrozenAmount();
				// 未冻结金额
				double diaounfrozen = diaoDanBean.getUnFrozenAmount();

				long settamount = 0;
				long settmerfee = 0;

				String settid = diaoDanBean.getSettleorderno();
				if(settid==null||"null".equals(settid)){
					settid = "";
				}
				for (SettingBean sett : settlist) {
					Double dou = (double) Math.round(Double.parseDouble(String
							.valueOf(sett.getMerchantsettleamt())) * 100 * 100) / 100;
					double settcaclu = Math.round(dou);
					// 调单金额大于
					if (diaounfrozen > settcaclu) {
						// 需要冻结的结算id保存下来。
						if ("".equals(settid)) {
							settid = String.valueOf(sett.getId());
						} else {
							settid = settid + ","
									+ String.valueOf(sett.getId());
						}
						settidlist.add(String.valueOf(sett.getId()));

						diaofrozen = diaofrozen + settcaclu;
						diaounfrozen = diaounfrozen - settcaclu;
						stuts = "0";
					} else if (diaounfrozen == settcaclu) {
						// 如果调单金额 = 结算金额，记录结算记录id，跳出循环；
						if ("".equals(settid)) {
							settid = String.valueOf(sett.getId());
						} else {
							settid = settid + ","
									+ String.valueOf(sett.getId());
						}
						settidlist.add(String.valueOf(sett.getId()));
						stuts = "1";
						diaofrozen = diaofrozen + settcaclu;
						diaounfrozen = diaounfrozen - diaounfrozen;
						break;
					} else if (diaounfrozen < settcaclu) {
						// 如果调单金额<结算金额，需要拆分结算记录，
						if ("".equals(settid)) {
							settid = String.valueOf(sett.getId());
						} else {
							settid = settid + ","
									+ String.valueOf(sett.getId());
						}
						settidlist.add(String.valueOf(sett.getId()));
						stuts = "1";
						SettingBean newsett = copysett(sett);
						// 更新老的结算金额。
						sett.setMerchantsettleamt(diaounfrozen / 100);
						sett.setTotalamt(sett.getTotalamt()
								- (settcaclu - diaounfrozen) / 100);
						diaodanService.updatesett(sett);
						// 插入新拆分的结算记录；
						newsett
								.setMerchantsettleamt((settcaclu - diaounfrozen) / 100);
						newsett.setTotalamt((settcaclu - diaounfrozen) / 100);
						newsett.setMerchantfee(0);
						// 生成一个新的批次号。年月日时分秒+六位随机数
						Random r = new Random();
						Double d = r.nextDouble();
						String s = d + "";
						s = s.substring(3, 3 + 6);
						String time = "";
						Date dt = new Date();
						SimpleDateFormat sdf = new SimpleDateFormat(
								"yyyyMMddHHmmss");
						time = sdf.format(dt);
						String ordno = time + s;
						newsett.setOrderno(ordno);
						Date date = new Date();
						newsett.setDt_create(date);
						diaodanService.insertsett(newsett);
						break;
					}
				}
				diaoDanBean.setStatus(stuts);
				diaoDanBean.setSettleorderno(settid);
				// 判断是否冻满。1为冻结满，0为未冻满；
				if ("1".equals(stuts)) {
					diaoDanBean.setDDamount(diaoamount);
					diaoDanBean.setFrozenAmount(diaoamount);
					diaoDanBean.setUnFrozenAmount(0);
				} else {
					diaoDanBean.setDDamount(diaoamount);
					diaoDanBean.setFrozenAmount(diaoamount - diaounfrozen);
					diaoDanBean.setUnFrozenAmount(diaounfrozen);
				}

				AuditingItem auditing = new AuditingItem();
				boolean b = diaodanService.diaodanApply(auditing, diaoDanBean,
						"1", settidlist);

				// //打入黑名单
				// String time="";
				// Date dt = new Date();
				// SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
				// time=sdf.format(dt);
				//			    
				// //判断黑名单是否存在。
				//			    
				// BlackList b= blackDao.queryByNo(dd.getAcctno(),null,null);
				// if(b==null||"".equals(b.getAcctNo())){
				// BlackList black = new BlackList();
				// black.setAcctNo(dd.getAcctno());
				// black.setMid(dd.getMerchantno());
				// black.setAddType("h4");
				// black.setDtCreate(time);
				// baseDao.editSave(black);
				// }
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage());
			System.out.println(e.getMessage());
			throw new ServiceException("执行失败~！");
		}
	}

	public SettingBean copysett(SettingBean sett) throws ServiceException {
		SettingBean newsett = new SettingBean();
		if (sett != null) {
			String ss = settflagserevice.getsettid();
			newsett.setId(Long.valueOf(ss));
			newsett.setAccountname(sett.getAccountname());
			newsett.setAccountno(sett.getAccountno());
			newsett.setBankdeposit(sett.getBankdeposit());
			newsett.setBatchno(sett.getBatchno());
			newsett.setChanalfee(sett.getChanalfee());
			newsett.setDatapath(sett.getDatapath());
			newsett.setDeposino(sett.getDeposino());
			newsett.setFreezeflag(sett.getFreezeflag());
			newsett.setMerchantfee(sett.getMerchantfee());
			newsett.setMerchantname(sett.getMerchantname());
			newsett.setMerchantno(sett.getMerchantno());
			newsett.setMerchantsettleamt(sett.getMerchantsettleamt());
			newsett.setOrderno(sett.getOrderno());
			newsett.setOrgno(sett.getOrgno());
			newsett.setRemark(sett.getRemark());
			newsett.setSetdate(sett.getSetdate());
			newsett.setSettledate(sett.getSettledate());
			newsett.setSettleflag(sett.getSettleflag());
			newsett.setTotalamt(sett.getTotalamt());
			newsett.setTotalnum(sett.getTotalnum());
			newsett.setTransenddate(sett.getTransenddate());
			newsett.setTransstartdate(sett.getTransstartdate());
			newsett.setUserid(sett.getUserid());
			newsett.setAcctype(sett.getAcctype());
			newsett.setLoadflag(sett.getLoadflag());
			newsett.setHostbranthno(sett.getHostbranthno());
			newsett.setNeedsett(sett.getNeedsett());
		}
		return newsett;
	}

	// 商户报表的生成
	public void excuMerchantReport() throws ServiceException {
		// TODO Auto-generated method stub
		// List<TransReport> transReportList = new ArrayList<TransReport>();
		SimpleDateFormat dft = new SimpleDateFormat("yyyy-MM-dd");
		Date beginDate = new Date();
		Calendar date = Calendar.getInstance();
		date.setTime(beginDate);
		date.set(Calendar.DATE, date.get(Calendar.DATE) - 1);
		Date endDate = date.getTime();
		try {
			String sql = "select m.merno,e.mer_name,c.mcc_type,e.mcc,em.area_code,m.amount,m.merfee,m.totoalnum,m.etsdate,m.avgamount,e.comm_mode "
					+ "from esmidinfo e,(select merno, etsdate, sum(t.amount) amount, sum(t.merfee) merfee,sum(t.calcuamount) calcuamount , count(t.amount) totoalnum,AVG(t.amount)  avgamount "
					+ "from t_transjnls_calcu t "
					+ "where t.sett_flag = 'Y' and t.etsdate='"
					+ Constance.formateDate(endDate,
							Constance.DATA_FORMAT_PATTERN_04)
					+ "'"
					+ " group by t.merno, t.etsdate) m,t_mcc_code c,esmidinfo_merinfo em "
					+ "where m.merno = e.mid  AND  e.mid=em.merchant AND c.mcc_code=e.mcc ";
			List<Object> objList = new ArrayList<Object>();
			objList = sqlBaseDao.queryAll(sql);
			if (objList != null && objList.size() > 0) {
				Iterator iterator = objList.iterator();
				while (iterator.hasNext()) {
					Object[] obj = (Object[]) iterator.next();
					TransReport rep = new TransReport();

					if (!Constance.Null2EmptyTrim(obj[0]).equals("")) {
						rep.setMerchantNo(obj[0].toString());
					}
					if (!Constance.Null2EmptyTrim(obj[1]).equals("")) {
						rep.setMerName(obj[1].toString());
					}
					if (!Constance.Null2EmptyTrim(obj[2]).equals("")) {
						rep.setMccType(obj[2].toString());
					}
					if (!Constance.Null2EmptyTrim(obj[3]).equals("")) {
						rep.setMcc(obj[3].toString());
					}
					if (!Constance.Null2EmptyTrim(obj[4]).equals("")) {
						rep.setAreaCode(obj[4].toString());
					}
					if (!Constance.null2Amt(obj[5]).equals("0.00")) {
						rep.setTotalAmt(Double.parseDouble(Constance
								.null2Amt(obj[5])));
					}
					if (!Constance.null2Amt(obj[6]).equals("0.00")) {
						rep.setTotalFee(Double.parseDouble(Constance
								.null2Amt(obj[6])));
					}
					if (!Constance.null2Amt(obj[7]).equals("0.00")) {
						rep.setTotalNum(Long.parseLong(Constance
								.null2Amt(obj[7])));
					}
					if (!Constance.Null2EmptyTrim(obj[8]).equals("")) {
						rep.setTransDate(obj[8].toString());
					}
					if (!Constance.null2Amt(obj[9]).equals("0.00")) {
						rep.setAvgAmt(Double.parseDouble(Constance
								.null2Amt(obj[9])));
					}
					if (!Constance.Null2EmptyTrim(obj[10]).equals("")) {
						rep.setFeeType(obj[10].toString());
					}
					baseDao.editSave(rep);

				}
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage());
			throw new ServiceException("商户交易报表执行失败~！");
		}

	}

	/**
	 * 查询需要发送的短信
	 * 
	 * @return
	 * @throws ServiceException
	 */
	public List<MessageSend> queryMesList() throws ServiceException {
		List<MessageSend> mesItemList = new ArrayList<MessageSend>();

		try {

			String sql = "select ID,PHONE_NUMBER,MES_CONTENT from T_MESSAGE_SEND where send_flag = '0' ";
			List<Object> objList = new ArrayList<Object>();
			objList = sqlBaseDao.queryAll(sql);
			if (objList != null && objList.size() > 0) {
				Iterator<Object> iterator = objList.iterator();
				while (iterator.hasNext()) {
					Object[] obj = (Object[]) iterator.next();
					MessageSend mes = new MessageSend();

					mes.setId(Long.valueOf(Constance.Null2EmptyTrim(obj[0])));
					mes.setPhoneNumber(Constance.Null2EmptyTrim(obj[1]));
					mes.setMesContent(Constance.Null2EmptyTrim(obj[2]));

					mesItemList.add(mes);
				}
			}
		} catch (Exception e) {
			logger.debug(e.getMessage());
			throw new ServiceException("查询代发短信表失败");
		}
		return mesItemList;
	}

	/**
	 * 更新短信状态
	 * 
	 * @param id
	 * @return
	 * @throws ServiceException
	 */
	public int updateMesFlag(Long id) throws ServiceException {
		int i = 0;
		String sql = "update T_MESSAGE_SEND set send_flag = '1',DT_LASTMOD = sysdate where id = "
				+ id;
		try {
			i = sqlBaseDao.deleteBySql(sql, null);
		} catch (Exception e) {
			logger.debug(e.getMessage());
			throw new ServiceException("更新短信状态失败,sql:" + sql);
		}
		return i;
	}

	public void excuDailiReport() throws ServiceException {
		SimpleDateFormat dft = new SimpleDateFormat("yyyy-MM-dd");
		Date beginDate = new Date();
		Calendar date = Calendar.getInstance();
		date.setTime(beginDate);
		date.set(Calendar.DATE, date.get(Calendar.DATE) - 1);
		Date endDate = date.getTime();

		// List<String> daiList=new ArrayList<String>();

		try {
			List<Object> objList = new ArrayList<Object>();
			objList = sqlBaseDao
					.queryAll("SELECT o.id FROM t_operators o WHERE o.role_type in('02','03') and s.status='00' ");

			for (int i = 0; i < objList.size(); i++) {

				String sql = "INSERT INTO T_MERCHANT_REPORT"
						+ "(ID,"
						+ " DT_CREATE,"
						+ " DT_LASTMOD,"
						+ " ACCOUNT_NO,"
						+ " REAL_NAME,"
						+ " ORG_CODE,"
						+ " MCC_TYPE,"
						+ " TOTAL_FEE,"
						+ " TOTAL_AMT,"
						+ " TOTAL_NUM,"
						+ " TRANS_DATE,"
						+ " TRANS_MONTH)"
						+ " SELECT SEQ_MERCHANT_REPORT.NEXTVAL,"
						+ " SYSDATE                     AS DT_CREATE,"
						+ " SYSDATE                     AS DT_LASTMOD,"
						+ " ACCOUNT_NO,"
						+ " REAL_NAME,"
						+ " ORG_CODE,"
						+ " MCC_TYPE,"
						+ " TOTAL_FEE,"
						+ " TOTAL_AMT,"
						+ " TOTAL_NUM,"
						+ " TRANS_DATE,"
						+ " TRANS_MONTH"
						+ " FROM ("
						+ " SELECT P.ACCOUNT_NO AS ACCOUNT_NO,"
						+ " P.REAL_NAME AS REAL_NAME,"
						+ " P.ORG_CODE AS ORG_CODE,"
						+ " TM.MCC_TYPE AS MCC_TYPE,"
						+ " SUM(TT.MERFEE) AS TOTAL_FEE,"
						+ " SUM(TT.AMOUNT) AS TOTAL_AMT,"
						+ " COUNT(TT.AMOUNT) AS TOTAL_NUM,"
						+ " TT.ETSDATE AS TRANS_DATE,"
						+ " SUBSTR(TT.ETSDATE, 1, 6) AS TRANS_MONTH"
						+ " FROM T_TRANSJNLS_CALCU TT,"
						+ " ESMIDINFO_MERINFO EM,"
						+ " (SELECT O.ACCOUNT_NO AS ACCOUNT_NO,"
						+ " O.REAL_NAME  AS REAL_NAME,"
						+ " O.ORG_CODE   AS ORG_CODE"
						+ " FROM T_OPERATORS O"
						+ " WHERE O.ID = "
						+ objList.get(i).toString()
						+ ") P,"
						+ " T_MCC_CODE TM"
						+ " WHERE TT.MERNO = EM.MERCHANT"
						+ " AND TT.MERTYPE = TM.MCC_CODE"
						+ " AND TT.SETT_FLAG = 'Y'"
						+ " AND TT.ETSDATE = '"
						+ Constance.formateDate(endDate,
								Constance.DATA_FORMAT_PATTERN_04)
						+ "'"
						+
						// " AND TT.ETSDATE = '"+20150126+"'"+
						" AND EM.AGENT_ID IN" + " (SELECT T.ID"
						+ " FROM T_OPERATORS T" + " WHERE T.ROLE_TYPE <> '15'"
						+ " START WITH ID = " + objList.get(i).toString()
						+ " CONNECT BY NOCYCLE PRIOR T.ID = T.SUPRI_ID)"
						+ " GROUP BY P.ORG_CODE," + " P.ACCOUNT_NO,"
						+ " P.REAL_NAME," + " TT.ETSDATE," + " TM.MCC_TYPE)";

				sqlBaseDao.executeBySql(sql);

				logger.info("更新了代理商id:" + objList.get(i).toString() + "报表");

			}

			logger.info("更新代理商日交易报表成功");

		} catch (DaoException e) {
			logger.debug(e.getMessage());
			throw new ServiceException("代理商日交易报表执行失败~！");
		}
	}

	/**
	 * 生成分润数据
	 * 
	 * @throws ServiceException
	 */
	public void excuProfitShare() throws ServiceException {

		logger.info("---------开始生成分润数据-----------");
		try {
			String startDate = DateUtil.getFirstDayOfLastMonth();
			String endDate = DateUtil.getLastDayOfLastMonth();

			// String startDate = "20141201";
			// String endDate = "20141231";
			String yearAndMonth = startDate.substring(0, 6);
			String remotePath = Utils.getConfigValue("excelFilePath");
			Date date = new Date();

			List<Operators> opList = imerchant.listAllAgent();
			// List<Operators> opList = imerchant.listLowOneMerchants(4200);

			logger.info("opList 长度" + opList.size());
			for (Operators op : opList) {
				String sql = ProfitShareQueryAction.getSql(op.getAccountNo(),
						startDate, endDate, op.getOrgCode(),false);
				List<ProfitShare> list = profitShareService
						.queryProfitShare(sql);
				logger.info("代理商:" + op.getAccountNo() + ",条数:" + list.size());
				for (ProfitShare pro : list) {
					pro.setAgentId(String.valueOf(op.getId()));
					pro.setTransdate(yearAndMonth);
					pro.setDtCreate(date);
					pro.setDtLastmod(date);
					baseDao.editSave(pro);
				}

				// 生成确认单
				if (list != null && list.size() > 0) {
					SXSSFWorkbook work = ExcelUtil.excelExport(op, list,
							startDate);
					createFile(work, remotePath, op, startDate);
				}

			}

		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ServiceException("执行失败~！");
		}

		logger.info("---------生成分润数据完成-----------");
	}

	public void createFile(SXSSFWorkbook work, String remotePath, Operators op,
			String date) throws Exception {

		String year = date.substring(0, 4);
		String month = date.substring(4, 6);
		String datePath = date.substring(0, 6);
		remotePath = remotePath + File.separator + datePath;// 按年月分目录

		File fileDir = new File(remotePath);
		if (!fileDir.exists()) {
			fileDir.mkdirs();
		}
		String fileName = new String((op.getRealName() + "_" + year + "年"
				+ month + "月分润").getBytes())
				+ ".xlsx";
		FileOutputStream fout = new FileOutputStream(remotePath
				+ File.separator + fileName);
		work.write(fout);
		fout.flush();

		fout.close();
		logger.info("文件保存路径【" + remotePath + File.separator + fileName + "】");

	}
	
	/**
	 * 生成分润数据 和天下
	 * 
	 * @throws ServiceException
	 */
	public void excuProfitShareHTX() throws ServiceException {

		logger.info("---------开始生成和天下分润数据-----------");
		try {
			Date date = new Date();
			String yesterday = DateUtil.getDate(date,-1,"yyyyMMdd");
			
			String yearAndMonth = yesterday.substring(0, 8);

			List<Operators> opList = imerchant.listAllAgent();
			// List<Operators> opList = imerchant.listLowOneMerchants(4200);

			logger.info("opList 长度" + opList.size());
			for (Operators op : opList) {
				String sql = ProfitShareQueryAction.getSql(op.getAccountNo(),
						yesterday, yesterday, op.getOrgCode(),true);
				List<ProfitShare> list = profitShareService
						.queryProfitShare(sql);
				logger.info("代理商:" + op.getAccountNo() + ",条数:" + list.size());
				for (ProfitShare pro : list) {
					pro.setAgentId(String.valueOf(op.getId()));
					pro.setTransdate(yearAndMonth);
					pro.setDtCreate(date);
					pro.setDtLastmod(date);
					baseDao.editSave(pro);
				}	

			}

		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ServiceException("执行失败~！");
		}

		logger.info("---------生成和天下分润数据完成-----------");
	}

}
